/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package tv.inventario.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.ejb.Stateless;
import javax.persistence.Query;
import tv.global.dao.BaseDAO;
import tv.inventario.modelo.Marca;
import tv.inventario.modelo.SubTipo;
import tv.inventario.modelo.TipoProducto;


/**
 *
 * @author ctrosch
 */
@Stateless
public class RubroClienteDAO extends BaseDAO {
    
    public List<TipoProducto> getTipoProductoByNrocta(Map<String,String> filtro){

        try {
            String subQuery = "SELECT DISTINCT i.tippro FROM RubrosCliente i ";
            subQuery += generarStringFiltro(filtro,"i", true);
            String sQuery = "SELECT t FROM TipoProducto t WHERE t.tippro IN ("+subQuery+")";
            sQuery += " ORDER BY t.descrp ASC";
            
            Query q = getEntityManager().createQuery(sQuery);

            return q.getResultList();

        }catch(Exception e){
            System.err.println("No se puede obtener la lista de tipos" );
            e.printStackTrace();
            return new ArrayList<TipoProducto>();
        }
    }

    public List<SubTipo> getSubtipoByNrocta(Map<String,String> filtro){

        try {
            String subQuery = "SELECT DISTINCT i.rubr01 FROM RubrosCliente i ";
            subQuery += generarStringFiltro(filtro,"i", true);

            String sQuery = "SELECT t FROM SubTipo t WHERE t.id.tippro= "+ filtro.get("tippro = ")
                    + " AND t.valrub IN ("+subQuery+")";
            sQuery += " ORDER BY t.descrp ASC";

            Query q = getEntityManager().createQuery(sQuery);
            return q.getResultList();

        }catch(Exception e){            
            System.err.println("No se puede obtener la lista de subtipos" );
            e.printStackTrace();
            return new ArrayList<SubTipo>();
        }
    }

    /**
     * Devuelve la lista de todas las marcas disponible que tenga el cliente en el catalogo
     *
     * @param filtro
     * @param esVendedor
     * @return
     */

    public List<Marca> getMarcaByNrocta(Map<String,String> filtro){

        try {
            String subQuery = "SELECT DISTINCT i.rubr02 FROM RubrosCliente i ";
            subQuery += generarStringFiltro(filtro,"i", true);

//            System.out.println("Filtra marca: " + subQuery);
 
            String sQuery = "SELECT t FROM Marca t WHERE t.id.tippro= " + filtro.get("tippro = ")
                    + " AND t.valrub IN ("+subQuery+")";
            sQuery += " ORDER BY t.descrp ASC";

            Query q = getEntityManager().createQuery(sQuery);
            
            return q.getResultList();

        }catch(Exception e){
            e.printStackTrace();
            System.out.println("No se puede obtener la lista de marcas" );
            return new ArrayList<Marca>();
        }
    }

    public List<String> getOrigenByNrocta(Map<String, String> filtro) {
        try {

            String sQuery = "SELECT DISTINCT i.origen FROM RubrosCliente i ";
            sQuery += generarStringFiltro(filtro,"i", true);
            Query q = getEntityManager().createQuery(sQuery);
            return q.getResultList();

        }catch(Exception e){
            e.printStackTrace();
            System.out.println("No se puede obtener la lista de origen" );
            return new ArrayList<String>();
        }
    }


    public List<String> getNovedadByNrocta(Map<String, String> filtro) {
        try {

            String sQuery = "SELECT DISTINCT i.novedad FROM RubrosCliente i ";
            sQuery += generarStringFiltro(filtro,"i", true);

//            System.out.println("Filtra novedades: " + sQuery);

            Query q = getEntityManager().createQuery(sQuery);
            return q.getResultList();

        }catch(Exception e){
            e.printStackTrace();
            System.out.println("No se puede obtener la lista de novedad" );
            return new ArrayList<String>();
        }
    }


    /**
     * public List<TipoProducto> getTipoProductoByNrocta(Map<String,String> filtro, boolean esVendedor){

        try {
            String sQuery = "SELECT t FROM TipoProducto t "
                    + " WHERE t.debaja =:debaja "
                    + " AND t.stocks = :stocks "
                    + " AND t.tippro >= :tipdesde "
                    + " AND t.tippro <= :tiphasta "
                    + " AND t.tippro IN "
                    + "                (SELECT DISTINCT i.tippro FROM RubrosCliente i"
                    + "                 WHERE i.nrocta =:nrocta ";

                    if (esVendedor){
                        sQuery += " ) " ;
                    }else{
                        sQuery += " AND i.diponibleEnInternet = 'S') ";
                    }

            sQuery += " ORDER BY t.descrp ASC";

            Query q = em.createQuery(sQuery);
            q.setParameter("debaja", "N");
            q.setParameter("stocks", "S");
            q.setParameter("tipdesde", "000");
            q.setParameter("tiphasta", "999");
            q.setParameter("nrocta", filtro.get("nrocta"));

            return q.getResultList();

        }catch(Exception e){
            e.printStackTrace();
            System.out.println("No se puede obtener la lista de tipos" );
            return new ArrayList<TipoProducto>();
        }
    }

    public List<SubTipo> getSubtipoByNrocta(Map<String,String> filtro, boolean esVendedor){

        try {
            String sQuery = "SELECT t FROM SubTipo t "
                    + " WHERE t.debaja =:debaja"
                    + " AND t.tipoProducto.tippro=:tipopro "
                    + " AND t.valrub IN (SELECT DISTINCT i.rubro1 FROM RubrosCliente i "
                                 + " WHERE i.nrocta =:nrocta AND i.tippro =:tippro ";
                    if (esVendedor){
                        sQuery += " ) " ;
                    }else{
                        sQuery += " AND i.diponibleEnInternet = 'S') ";
                    }

            sQuery += " ORDER BY t.descrp ASC";

            Query q = em.createQuery(sQuery);
            q.setParameter("nrocta",  filtro.get("nrocta"));
            q.setParameter("tippro",  filtro.get("tippro") );
            q.setParameter("tipopro", filtro.get("tippro"));
            q.setParameter("debaja", "N" );

            return q.getResultList();

        }catch(Exception e){
            e.printStackTrace();
            System.out.println("No se puede obtener la lista de marcas" );
            return new ArrayList<SubTipo>();
        }
    }

    public List<Marca> getMarcaByNrocta(Map<String,String> filtro, boolean esVendedor){

        try {
            String sQuery = "SELECT t FROM Marca t "
                    + " WHERE t.debaja =:debaja "
                    + " AND t.tipoProducto.tippro =:tippro "
                    + " AND t.valrub IN (SELECT DISTINCT i.rubro2 FROM RubrosCliente i "
                                        + "WHERE i.nrocta =:nrocta AND i.tippro =:tipopro ";
                    if (esVendedor){
                        sQuery += " ) " ;
                    }else{
                        sQuery += " AND i.diponibleEnInternet = 'S') ";
                    }
            sQuery += " ORDER BY t.descrp ASC";

            Query q = em.createQuery(sQuery);
            q.setParameter("debaja", "N");
            q.setParameter("nrocta",  filtro.get("nrocta"));
            q.setParameter("tippro",  filtro.get("tippro") );
            q.setParameter("tipopro",  filtro.get("tippro") );
            q.setParameter("subtipo", filtro.get("subtipo") );


            return q.getResultList();

        }catch(Exception e){
            e.printStackTrace();
            System.out.println("No se puede obtener la lista de subtipos" );
            return new ArrayList<Marca>();
        }
    }

    public List<String> getOrigenByNrocta(Map<String, String> filtro) {
        try {

            String sQuery = "SELECT DISTINCT i.origen FROM RubrosCliente i ";
            sQuery += generarStringFiltro(filtro,"i", true);
            sQuery += " ORDER BY i.origen ASC";

            Query q = em.createQuery(sQuery);

            return q.getResultList();

        }catch(Exception e){
            e.printStackTrace();
            System.out.println("No se puede obtener la lista de origen" );
            return new ArrayList<String>();
        }
    }


     */






    
    // Add business logic below. (Right-click in editor and choose
    // "Insert Code > Add Business Method")
 
}
